<?php

namespace App\Jobs\System;
// 加载采集环境

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;

class CheckAllTableCharsetJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct()
    {

    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $database = env('DB_DATABASE');
        $tables = DB::select("SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?", [$database]);

        foreach ($tables as $table) {
            $tableName = $table->TABLE_NAME;
            $tableCollation = $table->TABLE_COLLATION;

            if ($tableCollation !== 'utf8mb4_unicode_ci') {
                // Convert table character set and collation
                DB::statement("ALTER TABLE `{$tableName}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

                // Update each column character set and collation
                $columns = DB::select("SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?", [$database, $tableName]);

                foreach ($columns as $column) {
                    $columnName = $column->COLUMN_NAME;
                    $columnCharset = $column->CHARACTER_SET_NAME;
                    $columnCollation = $column->COLLATION_NAME;
                    $dataType = $column->DATA_TYPE;

                    // Only convert columns with string data types
                    if (in_array($dataType, ['char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext']) && ($columnCharset !== 'utf8mb4' || $columnCollation !== 'utf8mb4_unicode_ci')) {
                        DB::statement("ALTER TABLE `{$tableName}` MODIFY `{$columnName}` {$dataType}(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
                    }
                }
            }
        }
    }
}
